from flask import Blueprint, jsonify, request
from ..models.airport import Airport
from ..models.runway import Runway
from ..models.parking_apron import ParkingApron
from ..models.aircraft import Aircraft
from ..models.oil_tank import OilTank
from ..models.ammunition_depot import AmmunitionDepot
from .. import db
from sqlalchemy import func, case, and_, distinct, text
import math

bp = Blueprint('analysis', __name__, url_prefix='/api/analysis')

# 区域汇总接口 - 返回台北、台中、台南三个区域的机场和设施数量
@bp.route('/regional-summary', methods=['GET'])
def regional_summary():
    # 查询每个区域的机场数量
    region_counts = db.session.query(
        Airport.region,
        func.count(Airport.id).label('airport_count')
    ).group_by(Airport.region).all()
    
    # 整合数据
    result = []
    regions = {'taipei': '台北地区', 'taichung': '台中地区', 'tainan': '台南地区'}
    
    for region_code, region_name in regions.items():
        # 查找该区域的机场数量
        airports = next((count for r, count in region_counts if r == region_code), 0)
        
        # 构建返回数据
        result.append({
            'region': region_code,
            'name': region_name,
            'airports': airports
        })
    
    return jsonify(result)

# 根据区域获取机场基本信息
@bp.route('/airports', methods=['GET'])
def get_airports_by_region():
    """获取机场基本信息和设施数量"""
    region = request.args.get('region')
    
    try:
        # 导入text函数
        from sqlalchemy import text
        
        # 使用text()函数包装SQL，注意表名使用大写开头
        query = text("""
            SELECT 
                a.*,
                (SELECT COUNT(*) FROM Runways r WHERE r.airport_id = a.id) as runway_count,
                (SELECT COUNT(*) FROM ParkingAprons p WHERE p.airport_id = a.id) as apron_count,
                (SELECT COUNT(*) FROM OilTanks o WHERE o.airport_id = a.id) as oil_count,
                (SELECT COUNT(*) FROM AmmunitionDepots ad WHERE ad.airport_id = a.id) as ammo_count
            FROM Airports a
            WHERE (:region IS NULL OR :region = 'all' OR a.region = :region)
        """)
        
        # 执行查询
        results = db.session.execute(query, {"region": region}).fetchall()
        
        # 格式化结果
        formatted_result = []
        for result in results:
            # 获取设施数量
            runway_count = result.runway_count
            apron_count = result.apron_count
            oil_count = result.oil_count
            ammo_count = result.ammo_count
            
            # 计算总设施数
            facilities_count = runway_count + apron_count + oil_count + ammo_count
            
            # 计算能力值
            capability_value = 0
            if request.args.get('capability') == 'runways':
                capability_value = runway_count * 10
            elif request.args.get('capability') == 'parking-aprons':
                capability_value = apron_count * 8
            elif request.args.get('capability') == 'oil-tanks':
                capability_value = oil_count * 6
            elif request.args.get('capability') == 'ammunition-depots':
                capability_value = ammo_count * 7
            else:
                capability_value = facilities_count * 5
            
            # 打印调试信息
            if result.name == 'Q机场':
                print(f"Q机场的实际设施统计:")
                print(f"- 跑道: {runway_count}")
                print(f"- 停机坪: {apron_count}")
                print(f"- 油库: {oil_count}")
                print(f"- 弹药库: {ammo_count}")
                print(f"- 总设施数: {facilities_count}")
            
            # 构建返回数据
            airport_data = {
                'id': result.id,
                'name': result.name,
                'region': result.region,
                'latitude': float(result.latitude) if result.latitude else None,
                'longitude': float(result.longitude) if result.longitude else None,
                'facilitiesCount': facilities_count,
                'facilityDetails': {
                    'runways': runway_count,
                    'parkingAprons': apron_count,
                    'oilTanks': oil_count,
                    'ammunitionDepots': ammo_count
                },
                'capabilityValue': capability_value
            }
            
            formatted_result.append(airport_data)
        
        # 按设施总数降序排序
        formatted_result.sort(key=lambda x: x['facilitiesCount'], reverse=True)
        
        return jsonify(formatted_result)
        
    except Exception as e:
        print("Error in get_airports_by_region:", str(e))
        print("Error type:", type(e))
        import traceback
        print("Traceback:", traceback.format_exc())
        return jsonify({"error": str(e)}), 500

# 具有跑道的机场
@bp.route('/airports-with-runways', methods=['GET'])
def airports_with_runways():
    region = request.args.get('region')
    
    # 构建查询 - 查找有跑道的机场
    query = db.session.query(Airport).join(Runway, Runway.airport_id == Airport.id)
    if region and region != 'all':
        query = query.filter(Airport.region == region)
    
    # 使用distinct确保每个机场只返回一次
    airports = query.distinct().all()
    
    # 格式化结果
    result = []
    for airport in airports:
        # 获取该机场的设施数量
        runway_count = Runway.query.filter_by(airport_id=airport.id).count()
        apron_count = ParkingApron.query.filter_by(airport_id=airport.id).count()
        oil_count = OilTank.query.filter_by(airport_id=airport.id).count()
        ammo_count = AmmunitionDepot.query.filter_by(airport_id=airport.id).count()
        
        # 计算总设施数
        facilities_count = runway_count + apron_count + oil_count + ammo_count
        
        # 计算跑道能力值
        capability_value = runway_count * 10  # 假设每个跑道价值10分
        
        result.append({
            'id': airport.id,
            'name': airport.name,
            'region': airport.region,
            'latitude': airport.latitude,
            'longitude': airport.longitude,
            'facilitiesCount': facilities_count,
            'capabilityValue': capability_value,
            'hasRunways': runway_count > 0,
            'hasAprons': apron_count > 0,
            'hasOilTanks': oil_count > 0,
            'hasAmmunitionDepots': ammo_count > 0,
            'runways': runway_count > 0,
            'parkingAprons': apron_count > 0,
            'oilTanks': oil_count > 0,
            'ammunitionDepots': ammo_count > 0
        })
    
    return jsonify(result)

# 具有停机坪的机场
@bp.route('/airports-with-parking-aprons', methods=['GET'])
def airports_with_parking_aprons():
    region = request.args.get('region')
    
    # 构建查询 - 查找有停机坪的机场
    query = db.session.query(Airport).join(ParkingApron, ParkingApron.airport_id == Airport.id)
    if region and region != 'all':
        query = query.filter(Airport.region == region)
    
    # 使用distinct确保每个机场只返回一次
    airports = query.distinct().all()
    
    # 格式化结果
    result = []
    for airport in airports:
        # 获取该机场的设施数量
        runway_count = Runway.query.filter_by(airport_id=airport.id).count()
        apron_count = ParkingApron.query.filter_by(airport_id=airport.id).count()
        oil_count = OilTank.query.filter_by(airport_id=airport.id).count()
        ammo_count = AmmunitionDepot.query.filter_by(airport_id=airport.id).count()
        
        # 计算总设施数
        facilities_count = runway_count + apron_count + oil_count + ammo_count
        
        # 计算停机坪能力值
        capability_value = apron_count * 8  # 假设每个停机坪价值8分
        
        result.append({
            'id': airport.id,
            'name': airport.name,
            'region': airport.region,
            'latitude': airport.latitude,
            'longitude': airport.longitude,
            'facilitiesCount': facilities_count,
            'capabilityValue': capability_value,
            'hasRunways': runway_count > 0,
            'hasAprons': apron_count > 0,
            'hasOilTanks': oil_count > 0,
            'hasAmmunitionDepots': ammo_count > 0,
            'runways': runway_count > 0,
            'parkingAprons': apron_count > 0,
            'oilTanks': oil_count > 0,
            'ammunitionDepots': ammo_count > 0
        })
    
    return jsonify(result)

# 具有油库的机场
@bp.route('/airports-with-oil-tanks', methods=['GET'])
def airports_with_oil_tanks():
    region = request.args.get('region')
    
    # 构建查询 - 查找有油库的机场
    query = db.session.query(Airport).join(OilTank, OilTank.airport_id == Airport.id)
    if region and region != 'all':
        query = query.filter(Airport.region == region)
    
    # 使用distinct确保每个机场只返回一次
    airports = query.distinct().all()
    
    # 格式化结果
    result = []
    for airport in airports:
        # 获取该机场的设施数量
        runway_count = Runway.query.filter_by(airport_id=airport.id).count()
        apron_count = ParkingApron.query.filter_by(airport_id=airport.id).count()
        oil_count = OilTank.query.filter_by(airport_id=airport.id).count()
        ammo_count = AmmunitionDepot.query.filter_by(airport_id=airport.id).count()
        
        # 计算总设施数
        facilities_count = runway_count + apron_count + oil_count + ammo_count
        
        # 计算油库能力值
        capability_value = oil_count * 6  # 假设每个油库价值6分
        
        result.append({
            'id': airport.id,
            'name': airport.name,
            'region': airport.region,
            'latitude': airport.latitude,
            'longitude': airport.longitude,
            'facilitiesCount': facilities_count,
            'capabilityValue': capability_value,
            'hasRunways': runway_count > 0,
            'hasAprons': apron_count > 0,
            'hasOilTanks': oil_count > 0,
            'hasAmmunitionDepots': ammo_count > 0,
            'runways': runway_count > 0,
            'parkingAprons': apron_count > 0,
            'oilTanks': oil_count > 0,
            'ammunitionDepots': ammo_count > 0
        })
    
    return jsonify(result)

# 具有弹药库的机场
@bp.route('/airports-with-ammunition-depots', methods=['GET'])
def airports_with_ammunition_depots():
    region = request.args.get('region')
    
    # 构建查询 - 查找有弹药库的机场
    query = db.session.query(Airport).join(AmmunitionDepot, AmmunitionDepot.airport_id == Airport.id)
    if region and region != 'all':
        query = query.filter(Airport.region == region)
    
    # 使用distinct确保每个机场只返回一次
    airports = query.distinct().all()
    
    # 格式化结果
    result = []
    for airport in airports:
        # 获取该机场的设施数量
        runway_count = Runway.query.filter_by(airport_id=airport.id).count()
        apron_count = ParkingApron.query.filter_by(airport_id=airport.id).count()
        oil_count = OilTank.query.filter_by(airport_id=airport.id).count()
        ammo_count = AmmunitionDepot.query.filter_by(airport_id=airport.id).count()
        
        # 计算总设施数
        facilities_count = runway_count + apron_count + oil_count + ammo_count
        
        # 计算弹药库能力值
        capability_value = ammo_count * 7  # 假设每个弹药库价值7分
        
        result.append({
            'id': airport.id,
            'name': airport.name,
            'region': airport.region,
            'latitude': airport.latitude,
            'longitude': airport.longitude,
            'facilitiesCount': facilities_count,
            'capabilityValue': capability_value,
            'hasRunways': runway_count > 0,
            'hasAprons': apron_count > 0,
            'hasOilTanks': oil_count > 0,
            'hasAmmunitionDepots': ammo_count > 0,
            'runways': runway_count > 0,
            'parkingAprons': apron_count > 0,
            'oilTanks': oil_count > 0,
            'ammunitionDepots': ammo_count > 0
        })
    
    return jsonify(result)

# 起降能力分析
@bp.route('/takeoff-landing/initial', methods=['GET'])
def takeoff_landing_initial():
    # 提供初始数据
    runways = Runway.query.all()
    aircraft = Aircraft.query.all()
    
    # 生成散点图数据
    scatter_data = []
    for runway in runways:
        airport = Airport.query.get(runway.airport_id)
        if airport:
            scatter_data.append({
                'name': f"{airport.name}-{runway.name}",
                'value': [runway.length, runway.width],
                'increased_width': runway.increased_width,
                'airport_id': runway.airport_id
            })
    
    # 生成柱状图数据
    bar_data = []
    for aircraft_item in aircraft:
        window_value = calculate_window_value(runways, aircraft_item)
        bar_data.append({
            'name': aircraft_item.name,
            'value': window_value
        })
    
    return jsonify({
        'scatterData': scatter_data,
        'barData': bar_data
    })

@bp.route('/takeoff-landing/query', methods=['GET'])
def takeoff_landing_query():
    mission_type = request.args.get('missionType')
    aircraft_type = request.args.get('aircraftType')
    
    # 查询符合条件的飞机和跑道
    aircraft_query = Aircraft.query
    if mission_type:
        aircraft_query = aircraft_query.filter(Aircraft.role == mission_type)
    if aircraft_type:
        aircraft_query = aircraft_query.filter(Aircraft.name == aircraft_type)
    
    aircraft_list = aircraft_query.all()
    runways = Runway.query.all()
    
    # 生成散点图数据
    scatter_data = []
    for runway in runways:
        airport = Airport.query.get(runway.airport_id)
        if airport:
            scatter_data.append({
                'name': f"{airport.name}-{runway.name}",
                'value': [runway.length, runway.width],
                'increased_width': runway.increased_width,
                'airport_id': runway.airport_id
            })
    
    # 生成柱状图数据
    bar_data = []
    for aircraft_item in aircraft_list:
        window_value = calculate_window_value(runways, aircraft_item)
        bar_data.append({
            'name': aircraft_item.name,
            'value': window_value
        })
    
    return jsonify({
        'scatterData': scatter_data,
        'barData': bar_data
    })

# 停机能力分析
@bp.route('/parking/initial', methods=['GET'])
def parking_initial():
    parking_aprons = ParkingApron.query.all()
    aircraft = Aircraft.query.all()
    
    # 生成停机容量数据
    data = []
    for aircraft_item in aircraft:
        capacity = calculate_parking_capacity(parking_aprons, aircraft_item)
        data.append({
            'name': aircraft_item.name,
            'capacity': capacity
        })
    
    return jsonify({
        'parkingData': data
    })

@bp.route('/parking/query', methods=['GET'])
def parking_query():
    parking_method = request.args.get('parkingMethod')
    aircraft_type = request.args.get('aircraftType')
    
    # 查询符合条件的停机坪和飞机
    parking_query = ParkingApron.query
    if parking_method:
        parking_query = parking_query.filter(ParkingApron.parking_method == parking_method)
    
    parking_aprons = parking_query.all()
    
    aircraft_query = Aircraft.query
    if aircraft_type:
        aircraft_query = aircraft_query.filter(Aircraft.name == aircraft_type)
    
    aircraft_list = aircraft_query.all()
    
    # 生成停机容量数据
    data = []
    for aircraft_item in aircraft_list:
        capacity = calculate_parking_capacity(parking_aprons, aircraft_item)
        data.append({
            'name': aircraft_item.name,
            'capacity': capacity
        })
    
    return jsonify({
        'parkingData': data
    })

# 油料供应分析
@bp.route('/oil-supply/initial', methods=['GET'])
def oil_supply_initial():
    airports = Airport.query.all()
    
    # 为简单起见，模拟一些油库和连接数据
    nodes = []
    links = []
    
    # 添加机场节点
    for airport in airports:
        nodes.append({
            'id': f"airport-{airport.id}",
            'name': airport.name,
            'symbolSize': 30,
            'category': 0
        })
    
    # 添加示例油库和连接
    for i in range(1, 5):
        nodes.append({
            'id': f"oil-depot-{i}",
            'name': f"油库{i}",
            'symbolSize': 25,
            'category': 1
        })
        
        # 添加与机场的连接
        for airport in airports:
            if airport.id % 2 == i % 2:  # 简单的测试连接逻辑
                links.append({
                    'source': f"oil-depot-{i}",
                    'target': f"airport-{airport.id}"
                })
    
    categories = [
        {'name': '机场'},
        {'name': '油库'},
        {'name': '炼油厂'}
    ]
    
    return jsonify({
        'nodes': nodes,
        'links': links,
        'categories': categories
    })

@bp.route('/oil-supply/query', methods=['GET'])
def oil_supply_query():
    layout = request.args.get('layout', 'geographic')
    
    # 这里返回与初始化相同的数据，实际应用中应该根据查询条件过滤
    return oil_supply_initial()

# 弹药供应分析
@bp.route('/ammunition-supply/initial', methods=['GET'])
def ammunition_supply_initial():
    airports = Airport.query.all()
    
    # 为简单起见，模拟一些弹药库和连接数据
    nodes = []
    links = []
    
    # 添加机场节点
    for airport in airports:
        nodes.append({
            'id': f"airport-{airport.id}",
            'name': airport.name,
            'symbolSize': 30,
            'category': 0
        })
    
    # 添加示例弹药库和连接
    for i in range(1, 4):
        nodes.append({
            'id': f"ammo-depot-{i}",
            'name': f"弹药库{i}",
            'symbolSize': 25,
            'category': 1
        })
        
        # 添加与机场的连接
        for airport in airports:
            if airport.id % 3 == i % 3:  # 简单的测试连接逻辑
                links.append({
                    'source': f"ammo-depot-{i}",
                    'target': f"airport-{airport.id}"
                })
    
    categories = [
        {'name': '机场'},
        {'name': '弹药库'},
        {'name': '制造厂'}
    ]
    
    return jsonify({
        'nodes': nodes,
        'links': links,
        'categories': categories
    })

@bp.route('/ammunition-supply/query', methods=['GET'])
def ammunition_supply_query():
    layout = request.args.get('layout', 'geographic')
    
    # 这里返回与初始化相同的数据，实际应用中应该根据查询条件过滤
    return ammunition_supply_initial()

# 防护分析
@bp.route('/protection/initial', methods=['GET'])
def protection_initial():
    airports = Airport.query.all()
    
    # 模拟一些区域数据
    regions = []
    for airport in airports:
        regions.append({
            'name': airport.name,
            'value': airport.id * 10
        })
    
    return jsonify({
        'regions': regions
    })

@bp.route('/protection/query', methods=['GET'])
def protection_query():
    protection_type = request.args.get('protectionType')
    
    # 这里返回与初始化相同的数据，实际应用中应该根据查询条件过滤
    return protection_initial()

# 辅助函数
def calculate_window_value(runways, aircraft):
    """计算窗口值"""
    total_window_value = 0
    
    for runway in runways:
        # 找到满足条件的最小正整数 M 和 N
        m = 1
        while runway.width < m * aircraft.width:
            m += 1
        
        n = 1
        while runway.length < n * aircraft.length:
            n += 1
        
        # 窗口值 = M * N
        window_value = m * n
        total_window_value += window_value
    
    return total_window_value

def calculate_parking_capacity(parking_aprons, aircraft):
    """计算停机容量"""
    total_capacity = 0
    
    for apron in parking_aprons:
        if apron.parking_method == 'vertical':
            # 垂直停机
            w = aircraft.width
            l = aircraft.length
            
            # 简化的容量计算
            n_width = int(apron.width / (w * 1.2))
            n_depth = int(apron.net_depth / (l * 1.5))
            
            capacity = n_width * n_depth
            
        elif apron.parking_method == 'parallel':
            # 平行停机
            w = aircraft.width
            l = aircraft.length
            
            # 简化的容量计算
            n_width = int(apron.width / (l * 1.2))
            n_depth = int(apron.net_depth / (w * 1.5))
            
            capacity = n_width * n_depth
            
        elif apron.parking_method == 'pushback':
            # 顶推停机
            w = aircraft.width
            
            # 简化的容量计算
            capacity = int(apron.width / (w * 1.5))
            
        else:
            capacity = 0
        
        total_capacity += capacity
    
    return total_capacity

@bp.route('/parking-capacity', methods=['GET'])
def parking_capacity():
    """获取各机场停机能力分析数据
    
    返回各机场的当前部署飞机数量、本场停机容量（数据库记录）和过往停机容量（根据公式计算）
    """
    aircraft_id = request.args.get('aircraft_id', type=int)
    parking_method = request.args.get('parking_method')
    
    # 获取所有机场
    airports = Airport.query.all()
    
    # 如果没有指定飞机型号，默认使用第一个飞机
    selected_aircraft = None
    if aircraft_id:
        selected_aircraft = Aircraft.query.get(aircraft_id)
    else:
        selected_aircraft = Aircraft.query.first()
    
    if not selected_aircraft:
        return jsonify({"error": "未找到飞机数据"}), 404
    
    # 获取所有飞机类型（用于前端下拉选择）
    all_aircraft = Aircraft.query.all()
    aircraft_options = [{"value": a.id, "label": a.name} for a in all_aircraft]
    
    # 计算各机场停机能力
    results = []
    for airport in airports:
        # 查询该机场所有停机坪
        parking_apron_query = ParkingApron.query.filter_by(airport_id=airport.id)
        
        # 如果指定了停机方式，进行过滤
        if parking_method:
            parking_apron_query = parking_apron_query.filter_by(parking_method=parking_method)
        
        parking_aprons = parking_apron_query.all()
        
        # 计算历史停机容量
        historical_capacity = calculate_historical_capacity(airport, parking_aprons, selected_aircraft)
        
        # 构建结果
        result = {
            'id': airport.id,
            'name': airport.name,
            'region': airport.region,
            'current_capacity': airport.total_capacity,  # 当前容量直接取数据库值
            'historical_capacity': historical_capacity,  # 历史容量用公式计算
            'historical_total': sum(historical_capacity.values()),  # 历史容量总和
            'aircraft_count': airport.aircraft_count,   # 当前部署飞机数量
            'latitude': airport.latitude,
            'longitude': airport.longitude
        }
        results.append(result)
    
    # 添加停机方式选项
    parking_method_options = [
        {"value": "", "label": "全部"},
        {"value": "vertical", "label": "垂直停机"},
        {"value": "parallel", "label": "平行停机"},
        {"value": "pushback", "label": "顶推停机"}
    ]
    
    return jsonify({
        "data": results,
        "aircraft": {
            "selected": {
                "id": selected_aircraft.id,
                "name": selected_aircraft.name,
                "length": selected_aircraft.length,
                "width": selected_aircraft.width,
                "role": selected_aircraft.role
            },
            "options": aircraft_options
        },
        "parkingMethod": {
            "options": parking_method_options
        }
    })

def calculate_historical_capacity(airport, parking_aprons, aircraft):
    """计算历史停机容量"""
    # 获取W、L、I、T、P、C等参数
    W = aircraft.width
    L = aircraft.length
    I = airport.i_field
    T = airport.t_field
    P = airport.p_field
    C = airport.c_field
    
    # 初始化各种停机方式的容量
    vertical_capacity = 0
    parallel_capacity = 0
    pushback_capacity = 0
    
    # 遍历每个停机坪计算容量
    for apron in parking_aprons:
        # 获取停机坪宽度和深度
        width = apron.width
        depth = apron.net_depth
        
        # 根据停机方式计算容量
        if apron.parking_method == 'vertical':
            # 垂直停机
            # 尝试不同的N和M组合，找到最大容量
            max_capacity = 0
            
            # 计算合理的N和M范围上限
            max_n = max(1, int(width / (P + W)))
            max_m = max(1, int((depth - T - C) / (L + I + W/2)))
            
            for n in range(1, max_n + 1):
                for m in range(1, max_m + 1):
                    # 计算所需宽度和深度
                    required_width = 2*C + 2*T + n*(P+W) + W
                    required_depth = T + m*L + (2*m - 1)*(I + W/2) + C
                    
                    # 如果停机坪足够容纳这个配置
                    if required_width <= width and required_depth <= depth:
                        capacity = n * m  # 总容量 = 行数 * 列数
                        max_capacity = max(max_capacity, capacity)
            
            vertical_capacity += max_capacity
            
        elif apron.parking_method == 'parallel':
            # 平行停机
            # 尝试不同的N和M组合，找到最大容量
            max_capacity = 0
            
            # 计算合理的N和M范围上限
            max_n = max(1, int((depth - T - I - C) / (W + P - P/2)))
            max_m = max(1, int((width - 2*C - 2*T + 2*I) / (L + W + 2*T)))
            
            for n in range(1, max_n + 1):
                for m in range(1, max_m + 1):
                    # 计算所需宽度和深度
                    required_width = 2*C + 2*T + m*(L + W + 2*T) - 2*I
                    required_depth = T + (n + 0.5)*W + (n - 1)*P + I + C
                    
                    # 如果停机坪足够容纳这个配置
                    if required_width <= width and required_depth <= depth:
                        capacity = n * m  # 总容量 = 行数 * 列数
                        max_capacity = max(max_capacity, capacity)
            
            parallel_capacity += max_capacity
            
        elif apron.parking_method == 'pushback':
            # 顶推停机 (只考虑停1排)
            # 检查深度是否满足需求
            required_depth = W/2 + T + L
            
            if depth >= required_depth:
                # 计算可以停放的飞机数量
                n = int(width / (W + P))
                if n > 0:  # 确保至少能停一架
                    pushback_capacity += n
    
    return {
        'vertical': vertical_capacity,
        'parallel': parallel_capacity,
        'pushback': pushback_capacity
    }

# 添加一个专门针对Q机场的API端点用于调试
@bp.route('/airport/q', methods=['GET'])
def get_q_airport_data():
    """获取Q机场的详细信息"""
    try:
        # 使用原生SQL查询Q机场的设施数据
        query = text("""
            SELECT 
                a.*,
                (SELECT COUNT(*) FROM Runways r WHERE r.airport_id = a.id) as runway_count,
                (SELECT COUNT(*) FROM ParkingAprons p WHERE p.airport_id = a.id) as apron_count,
                (SELECT COUNT(*) FROM OilTanks o WHERE o.airport_id = a.id) as oil_count,
                (SELECT COUNT(*) FROM AmmunitionDepots ad WHERE ad.airport_id = a.id) as ammo_count
            FROM Airports a 
            WHERE a.name = 'Q机场'
        """)
        
        # 执行查询
        result = db.session.execute(query).fetchone()
        
        if not result:
            return jsonify({"error": "Q机场未找到"}), 404
            
        # 获取设施数量
        runway_count = result.runway_count
        apron_count = result.apron_count
        oil_count = result.oil_count
        ammo_count = result.ammo_count
        
        # 打印详细信息用于调试
        print(f"Q机场设施详情 (原生SQL):")
        print(f"- ID: {result.id}")
        print(f"- 名称: {result.name}")
        print(f"- 跑道数量: {runway_count}")
        print(f"- 停机坪数量: {apron_count}")
        print(f"- 油料设施数量: {oil_count}")
        print(f"- 弹药库数量: {ammo_count}")
        
        # 使用ORM方式获取相同数据作为对比
        airport = Airport.query.filter(Airport.name == 'Q机场').first()
        orm_runway_count = len(airport.runways)
        orm_apron_count = len(airport.parking_aprons)
        orm_oil_count = len(airport.oil_tanks)
        orm_ammo_count = len(airport.ammunition_depots)
        
        print(f"Q机场设施详情 (ORM):")
        print(f"- 跑道数量: {orm_runway_count}")
        print(f"- 停机坪数量: {orm_apron_count}")
        print(f"- 油料设施数量: {orm_oil_count}")
        print(f"- 弹药库数量: {orm_ammo_count}")
        
        # 返回详细信息
        return jsonify({
            'id': result.id,
            'name': result.name,
            'region': result.region,
            'latitude': float(result.latitude),
            'longitude': float(result.longitude),
            'facilitiesCount': runway_count + apron_count + oil_count + ammo_count,
            'facilityDetails': {
                'runways': runway_count,
                'parkingAprons': apron_count,
                'oilTanks': oil_count,
                'ammunitionDepots': ammo_count
            }
        })
    except Exception as e:
        print("获取Q机场数据错误:", str(e))
        print("异常类型:", type(e))
        import traceback
        print("堆栈信息:", traceback.format_exc())
        return jsonify({"error": str(e)}), 500 